package jm.nj.demo23main.a_dbcp;

import org.apache.commons.dbcp.BasicDataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;

public class DbcpDemo {

    @Test
    public void demo() throws SQLException {
        //创建连接池对象
/*        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql:///day23");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        //从连接池获取连接
        Connection conn = dataSource.getConnection();

        //正常的jdbc操作
        PreparedStatement pstmt = conn.prepareStatement("select  * from user");

        ResultSet rs = pstmt.executeQuery();
        while (rs.next()){
            System.out.println(rs.getInt("uid")+"   "+rs.getString("username")+"    "
            +rs.getString("password"));

        }

        rs.close();
        pstmt.close();*/

        //自己来
        BasicDataSource dataSource = new BasicDataSource();
        dataSource.setDriverClassName("com.mysql.jdbc.Driver");
        dataSource.setUrl("jbdc:mysql///day23");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");
        //从连接池获取连接
        Connection conn = dataSource.getConnection();
        //正常JDBC
        PreparedStatement pstmt = conn.prepareStatement("select * from user");
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getInt("uid") + "   " + rs.getString("username") + "  " + rs.getString("password"));
        }
        rs.close();
        pstmt.close();
        //归还对象
        /*
        * 对象连接池connection对象进行了增强,对close()的原功能进行了修改,由原来的关闭改为归还连接池
        *
        * */
        conn.close();
    }

    @Test
    public void demo2() throws Exception {
        //创建连接对象
        Properties prop = initConfig();
        DataSource dataSource = BasicDataSourceFactory.createDataSource(prop);
        //从连接池获取连接
        Connection conn = dataSource.getConnection();

        //操作
        PreparedStatement pstmt = conn.prepareStatement("select * from user");
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getInt("uid") + "  " + rs.getString("username") + "  " + rs.getString("password"));
        }

        rs.close();
        pstmt.close();
        // 归还连接对象
        /**
         * 连接池对connection对象进行了增强，把其close()方法的作用，由原来的关闭连接 改成了 把连接归还到连接池
         */
        conn.close();

    }

    @Test
    public void demo3() throws Exception {
        Properties prop = initConfig();
        DataSource dataSource = BasicDataSourceFactory.createDataSource(prop);

        Connection conn = dataSource.getConnection();

        PreparedStatement pstmt = conn.prepareStatement("select * from user");
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            System.out.println(rs.getInt("uid") + "  " + rs.getString("username") + "  " + rs.getString("password"));
        }
        rs.close();
        pstmt.close();
        conn.close();

    }


/*
    public Properties initConfig() {
        Properties prop = new Properties();
        InputStream is = null;

        try {
            is = this.getClass().getResourceAsStream("/dbcp-config1.properties");
            prop.load(is);
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                is.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return prop;

    }*/

    private Properties initConfig() {
        Properties prop = new Properties();
        InputStream is = null;
        try {
            is = this.getClass().getResourceAsStream("/dbcp-config1.properties");
            prop.load(is);
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            try {
                is.close();
            } catch (IOException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return prop;
    }


}
